import records
from sanic import Sanic
# 获取app实例
app = Sanic.get_app()

# 配置信息
config = app.config

class db(object):
    _db = None
    _table = None
    _alias = ''
    _field = '*'
    _oderBy = ''
    _limit = ''
    _where = ''
    _group = ''
    _join = ''
    _page = 1
    _pageSize = 10
    lastSql = ''

    def __init__(self, table=None):
        # self._db = records.Database("sqlite:///data.db").get_connection()
        self._db = config['DB_CONN'].get_connection()
        self._table = table

    # 获取字段
    def field(self, text="*"):
        self._field = text
        return self

    # 表别名
    def alias(self, text="*"):
        self._alias = text
        return self

    # 排序
    def join(self, table='', condition='', type='INNER'):
        self._join += ' %s JOIN %s ON %s ' %(type, table, condition)
        return self

    # 排序
    def order(self, text=""):
        self._oderBy = text
        return self

    def group(self, text=""):
        self._group = text
        return self

    # limit
    def limit(self, text="*"):
        self._limit = text
        sql = self.buildSql()
        # 条件
        if self._limit != '':
            sql += ' LIMIT '+self._limit
        print(sql)
        return self._db.query(sql)

    # 分页
    def page(self, page=1, size=10):
        self._page = page
        self._pageSize = size
        offset = (page - 1) * size
        return self.limit("%s,%s" %(offset, size))

    # where
    def where(self, where={}):
        self._where = where
        if not isinstance(where, str):
            self._where = ' AND '.join(self._where)
        return self

    # whereOr
    def whereOr(self, where={}):
        self._where = where
        if not isinstance(where, str):
            self._where = ' OR '.join(self._where)
        return self

    # 执行原生sql
    def query(self, sql=None):
        return self._db.query(sql)

    # 查询单条
    def find(self):
        sql = self.buildSql()
        return self._db.query(sql).first()

    # 查询全部
    def all(self):
        sql = self.buildSql()
        return self._db.query(sql)

    # 拼接sql
    def buildSql(self):
        sql = 'SELECT %s FROM %s ' %(self._field, self._table)

        # 别名
        if self._alias != '':
            sql += 'AS '+self._alias

        # join
        if self._join != '':
            sql += self._join

        # where
        if self._where != '':
            sql += ' where '+self._where

        # 分组
        if self._group != '':
            sql += ' GROUP BY '+self._group

        # 排序
        if self._oderBy != '':
            sql += ' ORDER BY '+self._oderBy

        # 记录最后一次sql
        self.lastSql = sql
        return sql
